package org.lt.cj.model.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import org.lt.cj.core.Seed;
import org.lt.cj.model.MyDataSource;
import org.lt.cj.model.dao.ICollectDao;
import org.lt.cj.model.pojo.Attribute;
import org.lt.cj.model.pojo.Category;
import org.lt.cj.model.pojo.Description;
import org.lt.cj.model.pojo.Evaluation;
import org.lt.cj.model.pojo.Product;
import org.lt.cj.model.pojo.Seller;

/**
 * @author zgh
 */
public class CollectImpl implements ICollectDao {

    private static final MyDataSource ds = new MyDataSource();
    private static Connection conn = null;

    //使用连接池
    public Connection getConnection() {
        return (Connection) ds.getConnection();
    }
    //使用同一个连接
//    public Connection getConnection() {
//        if (conn == null) {
//            conn = (Connection) ds.getConnection();
//        }
//        return conn;
//    }

    //释放操作
    public void closePsRs(PreparedStatement ps, ResultSet rs) {
        try {
            if (ps != null) {
                ps.close();
            }
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException ex) {
        }
    }

    //将连接还回连接池
    public void free(Connection conn) {
        ds.free(conn);
    }

    //保存已访问的链接
    public void insertUrl(String task, String url) {
        conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "INSERT INTO url VALUES(?,?,?)";
        try {
            ps = conn.prepareStatement(sql);
            long urlId = System.nanoTime();
            ps.setLong(1, urlId);
            ps.setString(2, task);
            ps.setString(3, url);
            ps.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            closePsRs(ps, rs);
            free(conn);
        }
    }

    //保存未访问的链接
    public void insertUnvisitUrl(String task, String url) {
        conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "INSERT INTO unvisit_url VALUES(?,?,?,?,?)";
        try {
            ps = conn.prepareStatement(sql);
            if (ps != null) {
                long urlId = System.nanoTime();
                ps.setLong(1, urlId);
                ps.setString(2, task);
                ps.setString(3, url);
                ps.executeUpdate();
            }
        } catch (SQLException ex) {
//            System.out.println("the too long url is:" + url);
            ex.printStackTrace();
        } finally {
            closePsRs(ps, rs);
            free(conn);
        }
    }

    //保存未访问的种子
    public void insertUnvisitSeed(String task, Seed seedUrl) {
        conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "INSERT INTO unvisit_url VALUES(?,?,?,?,?)";
        try {
            ps = conn.prepareStatement(sql);
            if (ps != null) {
                long urlId = System.nanoTime();
                ps.setLong(1, urlId);
                ps.setString(2, task);
                ps.setString(3, seedUrl.getUrl());
                ps.setString(4, seedUrl.getSeedName());
                ps.setString(5, seedUrl.getSortName());
                ps.executeUpdate();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            closePsRs(ps, rs);
            free(conn);
        }
    }

    //删除未访问url
    public void deleteUnvisitUrl(String unvisitUrl) {
        conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "DELETE FROM unvisit_url WHERE url = ?";
        try {
            ps = null;
            ps = conn.prepareStatement(sql);
            ps.setString(1, unvisitUrl);
            ps.executeUpdate();
            if (ps != null) {
                ps.close();
            }
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            closePsRs(ps, rs);
            free(conn);
        }
    }

    //删除未访问url集合
    public void deleteUnvisitUrlSet(Set<String> set) {
        Iterator it = set.iterator();
        while (it.hasNext()) {
            deleteUnvisitUrl((String) it.next());
        }
    }

    //删除未访问seed集合
    public void deleteUnvisitSeedList(List<Seed> seeds) {
        Iterator it = seeds.iterator();
        while (it.hasNext()) {
            Seed seed = (Seed) it.next();
            deleteUnvisitUrl(seed.getUrl());
        }
    }

    //按照任务名和数量来取得未访问URL数据
    public Set<String> getUnvisitedURLs(String taskname, int count) {
        Set<String> urlSet = new HashSet<String>();
        conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "SELECT url FROM unvisit_url WHERE task= ? LIMIT ?";
        try {

            ps = conn.prepareStatement(sql);
            ps.setString(1, taskname);
            ps.setInt(2, count);
            rs = ps.executeQuery();
            while (rs.next()) {
                urlSet.add(rs.getString(1));
            }
            if (ps != null) {
                ps.close();
            }
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            closePsRs(ps, rs);
            free(conn);
        }
        deleteUnvisitUrlSet(urlSet);
        return urlSet;
    }

    //查找数据库中是否有未访问的url
    public boolean UnvisitedURLsIsEmpty() {
        conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "SELECT COUNT(url) FROM unvisit_url";
        boolean empty = true;
        try {

            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            rs.setFetchSize(1);
            if (rs.next()) {
                if (rs.getInt(1) > 0) {
                    empty = false;
                }
            }
            if (ps != null) {
                ps.close();
            }
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            closePsRs(ps, rs);
            free(conn);
        }
        return empty;
    }

    //根据任务名查找是否有未访问的url
    public boolean UnvisitedURLsIsEmpty(String taskname) {
        conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "SELECT COUNT(url) FROM unvisit_url WHERE task = ?";
        long count = 0;
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, taskname);
            rs = ps.executeQuery();
            rs.setFetchSize(1);
            if (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            closePsRs(ps, rs);
            free(conn);
        }
        return count < 1;
    }

    /*
     * 查询url表是否已存在url,存在返回其id,不存在返回-1
     */
    public synchronized long existUrl(String url) {
        conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        long urlId = -1;
        String sql = "SELECT url_id FROM url WHERE url = ?";
        try {
            ps = conn.prepareStatement(sql);
            if (ps != null) {
                ps.setString(1, url);
                rs = ps.executeQuery();
            }
            if (rs != null && rs.next()) {
                urlId = rs.getLong(1);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            closePsRs(ps, rs);
            free(conn);
        }
        return urlId;
    }

    //对应任务的总链接数的查询
    public int getCountByTask(String task) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        conn = getConnection();
        int count = 0;
        String sql = "SELECT COUNT(task) FROM url WHERE task = ?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, task);
            rs = ps.executeQuery();
            if (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            closePsRs(ps, rs);
            free(conn);
        }
        return count;
    }

    public boolean contiansUnvisitedUrl(String url) {
        conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        long urlId = -1;
        String sql = "SELECT unvisit_id FROM unvisit_url WHERE url = ?";
        try {
            ps = conn.prepareStatement(sql);
            if (ps != null) {
                ps.setString(1, url);
                rs = ps.executeQuery();
            }
            if (rs != null && rs.next()) {
                urlId = rs.getLong(1);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            closePsRs(ps, rs);
            free(conn);
        }
        return urlId > 0;
    }

    //对应任务的总链接数的查询
    public int getUnvisitCountByTask(String task) {
        conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count = 0;

        String sql = "SELECT COUNT(task) FROM unvisit_url WHERE task = ?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, task);
            rs = ps.executeQuery();
            if (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            closePsRs(ps, rs);
            free(conn);
        }
        return count;
    }

    //对分类表进行操作
    public void insertCategory(long cid, String name, long firstCate, long secondCate, String lanFrom, String toLan, int updated) {
        conn = getConnection();
        System.out.println("---------" + conn);
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "INSERT INTO `" + lanFrom + "_category` (`cid`,`name`,`first_cate`,`second_cate`,`orig_lan`,`to_lan`,`updated`) VALUES(?,?,?,?,?,?,?)";
        try {
            ps = (PreparedStatement) conn.prepareStatement(sql);
            ps.setLong(1, cid);
            ps.setString(2, name);
            ps.setLong(3, firstCate);
            ps.setLong(4, secondCate);
            ps.setString(5, lanFrom);
            ps.setString(6, toLan);
            ps.setInt(7, updated);
            ps.executeUpdate();
        } catch (SQLException ex) {
        } finally {
            closePsRs(ps, rs);
            free(conn);
        }
    }

    //根据ID删除分类
    public void deleteCategory(long cid, String fromLan) {
        conn = getConnection();
        System.out.println("---------" + conn);
        PreparedStatement ps = null;
        String sql = "DELETE FROM `" + fromLan + "_category` WHERE cid=?";
        try {
            ps = (PreparedStatement) conn.prepareStatement(sql);
            ps.setLong(1, cid);
            ps.executeUpdate();
        } catch (SQLException ex) {
        } finally {
            closePsRs(ps, null);
            free(conn);
        }
    }

    //根据名称与父类ID查询分类是否存在，存在返回ID，不存在返回-1
    public long existCategory(String name, long firstId, long secondId, String fromLan) {
        conn = getConnection();
        System.out.println("---------" + conn);
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "SELECT cid FROM `" + fromLan + "_category` WHERE name=?";
        sql += firstId != 0 ? (" AND first_cate=" + firstId) : (" AND first_cate=0");
        sql += (firstId != 0 && secondId != 0) ? (" AND second_cate=" + secondId) : (" AND second_cate=0");
        long id = -1;
        try {
            ps = (PreparedStatement) conn.prepareStatement(sql);
            ps.setString(1, name);
            rs = ps.executeQuery();
            while (rs.next()) {
                id = rs.getLong(1);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            closePsRs(ps, rs);
            free(conn);
        }
        return id;
    }

    //查询分类信息，numbers指定数量
    public List<Category> getCategory(String language, int numbers) {
        conn = getConnection();
        System.out.println("---------" + conn);
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Category> list = new ArrayList<Category>();

        if (language != null && !language.equals("")) {
            String sql = "SELECT * FROM `" + language + "_category` WHERE to_lan ='' LIMIT ?";

            Category c = null;
            try {
                ps = conn.prepareStatement(sql);
                ps.setInt(1, numbers);
                rs = ps.executeQuery();
                while (rs.next()) {
                    c = new Category();
                    c.setId(rs.getLong(1));
                    c.setName(rs.getString(2));
                    c.setFirstCate(rs.getLong(3));
                    c.setSecondCate(rs.getLong(4));
                    c.setOrigLan(rs.getString(5));
                    c.setToLan(rs.getString(6));
                    c.setUpdated(rs.getInt(7));
                    list.add(c);
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            } finally {
                closePsRs(ps, rs);
                free(conn);
            }
        }
        return list;
    }

    //将分类设置为已翻译
    public void setCategoryTranslated(long cid, String fromLan, String toLan) {
        conn = getConnection();
        System.out.println("---------" + conn);
        PreparedStatement ps = null;
        String sql = "UPDATE `" + fromLan + "_category` SET to_lan=? WHERE cid=?";
        try {
            ps = (PreparedStatement) conn.prepareStatement(sql);
            ps.setString(1, toLan);
            ps.setLong(2, cid);
            ps.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            closePsRs(ps, null);
            free(conn);
        }
    }

    //插入商家信息
    public void insertSeller(long sid, String name, String company, String location, String site, String imNumber, String fromLan, String toLan, int updated) {
        conn = getConnection();
        System.out.println("---------" + conn);
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "INSERT INTO `" + fromLan + "_seller` (`sid`,`seller_name`,`company`,`location`,`site`,`im_number`,`orig_lan`,`to_lan`,`updated`) VALUES(?,?,?,?,?,?,?,?,?)";
        try {
            ps = (PreparedStatement) conn.prepareStatement(sql);
            ps.setLong(1, sid);
            ps.setString(2, name);
            ps.setString(3, company);
            ps.setString(4, location);
            ps.setString(5, site);
            ps.setString(6, imNumber);
            ps.setString(7, fromLan);
            ps.setString(8, toLan);
            ps.setInt(9, updated);
            ps.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            closePsRs(ps, rs);
            free(conn);
        }
    }

    //根据ID删除商家息
    public void deleteSeller(long sid, String fromLan) {
        conn = getConnection();
        System.out.println("---------" + conn);
        PreparedStatement ps = null;
        String sql = "DELETE FROM `" + fromLan + "_seller` WHERE sid=?";
        try {
            ps = (PreparedStatement) conn.prepareStatement(sql);
            ps.setLong(1, sid);
            ps.executeUpdate();
        } catch (SQLException ex) {
        } finally {
            closePsRs(ps, null);
            free(conn);
        }
    }

    //根据名称查询商家是否存在，存在返回ID，不存在返回-1
    public long existSeller(String name, String fromLan) {
        conn = getConnection();
        System.out.println("---------" + conn);
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "SELECT sid FROM `" + fromLan + "_seller` WHERE seller_name=?";
        long id = -1;
        try {
            ps = (PreparedStatement) conn.prepareStatement(sql);
            ps.setString(1, name);
            rs = ps.executeQuery();
            while (rs.next()) {
                id = rs.getLong(1);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            closePsRs(ps, rs);
            free(conn);
        }
        return id;
    }

    //查询商家信息，numbers指定数量
    public List<Seller> getSeller(String language, int numbers) {
        conn = getConnection();
        System.out.println("---------" + conn);
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Seller> list = new ArrayList<Seller>();

        if (language != null && !language.equals("")) {
            String sql = "SELECT * FROM `" + language + "_seller` WHERE to_lan ='' LIMIT ?";

            Seller s = null;
            try {
                ps = conn.prepareStatement(sql);
                ps.setInt(1, numbers);
                rs = ps.executeQuery();
                while (rs.next()) {
                    s = new Seller();
                    s.setId(rs.getLong(1));
                    s.setSellerName(rs.getString(2));
                    s.setCompany(rs.getString(3));
                    s.setLocation(rs.getString(4));
                    s.setSite(rs.getString(5));
                    s.setImNumber(rs.getString(6));
                    s.setOrigLan(rs.getString(7));
                    s.setToLan(rs.getString(8));
                    s.setUpdated(rs.getInt(9));
                    list.add(s);
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            } finally {
                closePsRs(ps, rs);
                free(conn);
            }
        }
        return list;
    }

    //将商家信息设置为已翻译
    public void setSellerTranslated(long sid, String fromLan, String toLan) {
        conn = getConnection();
        System.out.println("---------" + conn);
        PreparedStatement ps = null;
        String sql = "UPDATE `" + fromLan + "_seller` SET to_lan=? WHERE sid=?";
        try {
            ps = (PreparedStatement) conn.prepareStatement(sql);
            ps.setString(1, toLan);
            ps.setLong(2, sid);
            ps.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            closePsRs(ps, null);
            free(conn);
        }
    }

    //对商品表进行操作
    //插入商品信息
    public void insertProduct(long pid, long cateId, long sellerId, String name, double price, int sellCount, String collectTime, String url, String fromLan, String toLan, int updated) {
        conn = getConnection();
        System.out.println("---------" + conn);
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "INSERT INTO `" + fromLan + "_product` (`pid`,`cateid`,`sellerid`,`name`,`price`,`sell_count`,`collect_time`,`url`,`orig_lan`,`to_lan`,`updated`) VALUES(?,?,?,?,?,?,?,?,?,?,?)";
        try {
            ps = (PreparedStatement) conn.prepareStatement(sql);
            ps.setLong(1, pid);
            ps.setLong(2, cateId);
            ps.setLong(3, sellerId);
            ps.setString(4, name);
            ps.setDouble(5, price);
            ps.setInt(6, sellCount);
            ps.setString(7, collectTime);
            ps.setString(8, url);
            ps.setString(9, fromLan);
            ps.setString(10, toLan);
            ps.setInt(11, updated);
            ps.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            closePsRs(ps, rs);
            free(conn);
        }
    }

    //根据ID删除商品信息
    public void deleteProduct(long pid, String fromLan) {
        conn = getConnection();
        System.out.println("---------" + conn);
        PreparedStatement ps = null;
        String sql = "DELETE FROM `" + fromLan + "_product` WHERE pid=?";
        try {
            ps = (PreparedStatement) conn.prepareStatement(sql);
            ps.setLong(1, pid);
            ps.executeUpdate();
        } catch (SQLException ex) {
        } finally {
            closePsRs(ps, null);
            free(conn);
        }
    }

    //根据url查询商品是否存在，存在返回ID，不存在返回-1
    public long existProduct(String url, String fromLan) {
        conn = getConnection();
        System.out.println("---------" + conn);
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "SELECT pid FROM `" + fromLan + "_product` WHERE url=?";
        long id = -1;
        try {
            ps = (PreparedStatement) conn.prepareStatement(sql);
            ps.setString(1, url);
            rs = ps.executeQuery();
            while (rs.next()) {
                id = rs.getLong(1);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            closePsRs(ps, rs);
            free(conn);
        }
        return id;
    }

    //查询商品信息，numbers指定数量
    public List<Product> getProduct(String language, int numbers) {
        conn = getConnection();
        System.out.println("---------" + conn);
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Product> list = new ArrayList<Product>();

        if (language != null && !language.equals("")) {
            String sql = "SELECT * FROM `" + language + "_product` WHERE to_lan ='' LIMIT ?";

            Product p = null;
            try {
                ps = conn.prepareStatement(sql);
                ps.setInt(1, numbers);
                rs = ps.executeQuery();
                while (rs.next()) {
                    p = new Product();
                    p.setId(rs.getLong(1));
                    p.setCateid(rs.getLong(2));
                    p.setSellerid(rs.getLong(3));
                    p.setName(rs.getString(4));
                    p.setPrice(rs.getDouble(5));
                    p.setSellCount(rs.getInt(6));
                    p.setCollectTime(rs.getString(7));
                    p.setUrl(rs.getString(8));
                    p.setOrigLan(rs.getString(9));
                    p.setToLan(rs.getString(10));
                    p.setUpdated(rs.getInt(11));
                    list.add(p);
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            } finally {
                closePsRs(ps, rs);
                free(conn);
            }
        }
        return list;
    }

    //将商品信息设置为已翻译
    public void setProductTranslated(long pid, String fromLan, String toLan) {
        conn = getConnection();
        System.out.println("---------" + conn);
        PreparedStatement ps = null;
        String sql = "UPDATE `" + fromLan + "_product` SET to_lan=? WHERE pid=?";
        try {
            ps = (PreparedStatement) conn.prepareStatement(sql);
            ps.setString(1, toLan);
            ps.setLong(2, pid);
            ps.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            closePsRs(ps, null);
            free(conn);
        }
    }

    //对属性表进行操作
    //插入属性信息
    public void insertAttribute(long aid, long productId, String name, String value, String fromLan, String toLan, int updated) {
        conn = getConnection();
        System.out.println("---------" + conn);
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "INSERT INTO `" + fromLan + "_attribute` (`aid`,`productid`,`name`,`value`,`orig_lan`,`to_lan`,`updated`) VALUES(?,?,?,?,?,?,?)";
        try {
            ps = (PreparedStatement) conn.prepareStatement(sql);
            ps.setLong(1, aid);
            ps.setLong(2, productId);
            ps.setString(3, name);
            ps.setString(4, value);
            ps.setString(5, fromLan);
            ps.setString(6, toLan);
            ps.setInt(7, updated);
            ps.executeUpdate();
        } catch (SQLException ex) {
        } finally {
            closePsRs(ps, rs);
            free(conn);
        }
    }

    //根据ID删除属性信息
    public void deleteAttribute(long aid, String fromLan) {
        conn = getConnection();
        System.out.println("---------" + conn);
        PreparedStatement ps = null;
        String sql = "DELETE FROM `" + fromLan + "_attribute` WHERE aid=?";
        try {
            ps = (PreparedStatement) conn.prepareStatement(sql);
            ps.setLong(1, aid);
            ps.executeUpdate();
        } catch (SQLException ex) {
        } finally {
            closePsRs(ps, null);
            free(conn);
        }
    }

    //查询属性信息，numbers指定数量
    public List<Attribute> getAttribute(String language, int numbers) {
        conn = getConnection();
        System.out.println("---------" + conn);
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Attribute> list = new ArrayList<Attribute>();

        if (language != null && !language.equals("")) {
            String sql = "SELECT * FROM `" + language + "_attribute` WHERE to_lan ='' LIMIT ?";

            Attribute a = null;
            try {
                ps = conn.prepareStatement(sql);
                ps.setInt(1, numbers);
                rs = ps.executeQuery();
                while (rs.next()) {
                    a = new Attribute();
                    a.setId(rs.getLong(1));
                    a.setProductid(rs.getLong(2));
                    a.setName(rs.getString(3));
                    a.setValue(rs.getString(4));
                    a.setOrigLan(rs.getString(5));
                    a.setToLan(rs.getString(6));
                    a.setUpdated(rs.getInt(7));
                    list.add(a);
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            } finally {
                closePsRs(ps, rs);
                free(conn);
            }
        }
        return list;
    }

    //将属性信息设置为已翻译
    public void setAttrebuteTranslated(long aid, String fromLan, String toLan) {
        conn = getConnection();
        System.out.println("---------" + conn);
        PreparedStatement ps = null;
        String sql = "UPDATE `" + fromLan + "_attribute` SET to_lan=? WHERE aid=?";
        try {
            ps = (PreparedStatement) conn.prepareStatement(sql);
            ps.setString(1, toLan);
            ps.setLong(2, aid);
            ps.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            closePsRs(ps, null);
            free(conn);
        }
    }

    //对详情表进行操作
    //插入详情信息
    public void insertDescription(long did, long productId, String content, String fromLan, String toLan, int updated) {
        conn = getConnection();
        System.out.println("---------" + conn);
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "INSERT INTO `" + fromLan + "_description` (`did`,`productid`,`content`,`orig_lan`,`to_lan`,`updated`) VALUES(?,?,?,?,?,?)";
        try {
            ps = (PreparedStatement) conn.prepareStatement(sql);
            ps.setLong(1, did);
            ps.setLong(2, productId);
            ps.setString(3, content);
            ps.setString(4, fromLan);
            ps.setString(5, toLan);
            ps.setInt(6, updated);
            ps.executeUpdate();
        } catch (SQLException ex) {
        } finally {
            closePsRs(ps, rs);
            free(conn);
        }
    }

    //根据ID删除详情信息
    public void deleteDescription(long did, String fromLan) {
        conn = getConnection();
        System.out.println("---------" + conn);
        PreparedStatement ps = null;
        String sql = "DELETE FROM `" + fromLan + "_description` WHERE did=?";
        try {
            ps = (PreparedStatement) conn.prepareStatement(sql);
            ps.setLong(1, did);
            ps.executeUpdate();
        } catch (SQLException ex) {
        } finally {
            closePsRs(ps, null);
            free(conn);
        }
    }

    //查询详情信息，numbers指定数量
    public List<Description> getDescription(String language, int numbers) {
        conn = getConnection();
        System.out.println("---------" + conn);
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Description> list = new ArrayList<Description>();

        if (language != null && !language.equals("")) {
            String sql = "SELECT * FROM `" + language + "_description` WHERE to_lan ='' LIMIT ?";

            Description d = null;
            try {
                ps = conn.prepareStatement(sql);
                ps.setInt(1, numbers);
                rs = ps.executeQuery();
                while (rs.next()) {
                    d = new Description();
                    d.setId(rs.getLong(1));
                    d.setProductid(rs.getLong(2));
                    d.setContent(rs.getString(3));
                    d.setOrigLan(rs.getString(4));
                    d.setToLan(rs.getString(5));
                    d.setUpdated(rs.getInt(6));
                    list.add(d);
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            } finally {
                closePsRs(ps, rs);
                free(conn);
            }
        }
        return list;
    }

    //将详情信息设置为已翻译
    public void setDescriptionTranslated(long did, String fromLan, String toLan) {
        conn = getConnection();
        System.out.println("---------" + conn);
        PreparedStatement ps = null;
        String sql = "UPDATE `" + fromLan + "_description` SET to_lan=? WHERE did=?";
        try {
            ps = (PreparedStatement) conn.prepareStatement(sql);
            ps.setString(1, toLan);
            ps.setLong(2, did);
            ps.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            closePsRs(ps, null);
            free(conn);
        }
    }

    //对评价表进行操作
    //插入评价信息
    public void insertEvaluation(long eid, long productId, String content, String time, String fromLan, String toLan, int updated) {
        conn = getConnection();
        System.out.println("---------" + conn);
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "INSERT INTO `" + fromLan + "_evaluation` (`eid`,`productid`,`content`,`time`,`orig_lan`,`to_lan`,`updated`) VALUES(?,?,?,?,?,?,?)";
        try {
            ps = (PreparedStatement) conn.prepareStatement(sql);
            ps.setLong(1, eid);
            ps.setLong(2, productId);
            ps.setString(3, content);
            ps.setString(4, time);
            ps.setString(5, fromLan);
            ps.setString(6, toLan);
            ps.setInt(7, updated);
            ps.executeUpdate();
        } catch (SQLException ex) {
        } finally {
            closePsRs(ps, rs);
            free(conn);
        }
    }

    //根据ID删除评价信息
    public void deleteEvaluation(long eid, String fromLan) {
        conn = getConnection();
        System.out.println("---------" + conn);
        PreparedStatement ps = null;
        String sql = "DELETE FROM `" + fromLan + "_evaluation` WHERE eid=?";
        try {
            ps = (PreparedStatement) conn.prepareStatement(sql);
            ps.setLong(1, eid);
            ps.executeUpdate();
        } catch (SQLException ex) {
        } finally {
            closePsRs(ps, null);
            free(conn);
        }
    }

    //查询评价信息，numbers指定数量
    public List<Evaluation> getEvaluation(String language, int numbers) {
        conn = getConnection();
        System.out.println("---------" + conn);
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Evaluation> list = new ArrayList<Evaluation>();

        if (language != null && !language.equals("")) {
            String sql = "SELECT * FROM `" + language + "_evaluation` WHERE to_lan ='' LIMIT ?";

            Evaluation e = null;
            try {
                ps = conn.prepareStatement(sql);
                ps.setInt(1, numbers);
                rs = ps.executeQuery();
                while (rs.next()) {
                    e = new Evaluation();
                    e.setId(rs.getLong(1));
                    e.setProductid(rs.getLong(2));
                    e.setContent(rs.getString(3));
                    e.setTime(rs.getString(4));
                    e.setOrigLan(rs.getString(5));
                    e.setToLan(rs.getString(6));
                    e.setUpdated(rs.getInt(7));
                    list.add(e);
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            } finally {
                closePsRs(ps, rs);
                free(conn);
            }
        }
        return list;
    }

    //将评价信息设置为已翻译
    public void setEvaluationTranslated(long eid, String fromLan, String toLan) {
        conn = getConnection();
        System.out.println("---------" + conn);
        PreparedStatement ps = null;
        String sql = "UPDATE `" + fromLan + "_evaluation` SET to_lan=? WHERE eid=?";
        try {
            ps = (PreparedStatement) conn.prepareStatement(sql);
            ps.setString(1, toLan);
            ps.setLong(2, eid);
            ps.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            closePsRs(ps, null);
            free(conn);
        }
    }

    public List<Seed> getUnvisitedSeeds(String taskname, int count) {
//        Set<String> urlSet = new HashSet<String>();
        List<Seed> seedList = new ArrayList();
        conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "SELECT url,url_name,sort_name FROM unvisit_url WHERE task= ? LIMIT ?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, taskname);
            ps.setInt(2, count);
            rs = ps.executeQuery();
            while (rs.next()) {
                Seed seed = new Seed();
                seed.setUrl(rs.getString(1));
                seed.setSeedName(rs.getString(2));
                seed.setSortName(rs.getString(3));
            }
            if (ps != null) {
                ps.close();
            }
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            closePsRs(ps, rs);
            free(conn);
        }
//        deleteUnvisitUrlSet(urlSet);
        deleteUnvisitSeedList(seedList);
        return seedList;
    }
}
